import Router from '@koa/router';
import response from '../response.js';
import { qipiao_get, qipiao_post, APPID } from '../qipiao.js';
import db from '../mysqlHelper.js';
import { createToken } from '../auth.js';
import moment from 'moment';
import config from '../configuration.js';

const router = new Router();


const BASEURL = config.baseUrl;


import crypto from 'crypto';


// 读取公钥
const publicKey = `MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA5J4IT8QyMcqAXvWYD55N1rpRX5LxRail/Vt+slsFaSPOTGDONMqXil6dBswNPB4Du3Id8fgdjagcQsm/e5CpLXb1l+vf5/X2W8bQLVZnzhrbFl1lJErWYLrZQ6/IBwPlswHf7lYCYuThcToXKHRp/r7QHdMX6hp00Mcr2SGcqltdfgC9SMKnP8WjZnLP+m7BYtydvN2R5BiMnTf2i/8x/WQUM92KwbYGp8ISUfU2+PBcVw7kNaMngzElfWZTK0fvfj/nKGCWAN3Jx4b14djSQi1lKO56UD5J8oBGLw9nkwDjgTOqqeeSmPzU2Z+ILpECcsbFyVaLEvwSjBDfnx8PJQIDAQAB`;
//将公钥转换为pem格式
const pem =
    `-----BEGIN PUBLIC KEY-----\n` +
    publicKey.match(/.{1,64}/g).join('\n') +
    `\n-----END PUBLIC KEY-----`;
function verifySignature(jsonData, timestamp, signature) {
    const verify = crypto.createVerify('SHA256');
    verify.update(jsonData + timestamp, 'utf8');
    verify.end();
    if (signature == undefined) {
        console.log('有票票签名为空');
        return true
    }
    const isValid = verify.verify(pem, signature, 'base64');
    return isValid;
}





async function findRate(isp_code, category_id) {
    let rate = await db.findOne('isp_price_set', { isp_code: isp_code, category_id: category_id });
    if (rate instanceof Error) {
        return { err: '数据库查询错误:' + rate.message };
    }
    if (rate == null) {
        return { rate: 100 }
    }
    else {
        return { rate: rate.increase_ratio }
    }
}

async function setPrice(isp_code, category_id, obj, findKey,) {
    let rate = await findRate(isp_code, category_id);
    console.log('rate:', JSON.stringify(rate));
    if (rate.rate == 100) {
        return;
    } else {
        modfiyPrice(obj, findKey, rate.rate);
    }
}

function modfiyPrice(obj, findKey, rate) {
    for (let key in obj) {
        if (obj.hasOwnProperty(key)) {
            if (typeof obj[key] === 'object' && obj[key] !== null) {
                modfiyPrice(obj[key], findKey, rate);
            } else if (key === findKey) {
                let temp_rate = rate / 100;
                let ysjg = obj[key];
                obj[key] = Math.round(ysjg * temp_rate * 10) / 10;
                // console.log("发现目标", `Key:${key} 修改前价格:${ysjg} 修改后价格${obj[key]} 调整倍数:${temp_rate}`);
            }
        }
    }
}
//增加一个接口，用动态路由的方式根据路由调用不同的方法
router.all('/isp/api/:category/:command', async (ctx) => {
    //打印路径参数
    let path = '/api/' + ctx.params.category + '/' + ctx.params.command;
    //打印请求方式是post还是get
    console.log(ctx.request.method);

    if (ctx.state?.isp == undefined) {
        ctx.body = response.fail('请先获取token');
        return;
    }
    let res = null;
    console.log('isp:', JSON.stringify(ctx.state.isp));
    let isp_code = ctx.state.isp.code;
    if (ctx.request.method == 'GET') {
        console.log(ctx.request.query);
        let res = await qipiao_get(path, ctx.request.query);
        if (res.err) {
            ctx.body = response.fail(res.err);
            return;
        }

        switch (ctx.params.command) {
            case 'cinema_shows':
                await setPrice(isp_code, 0, res, 'price'); //按电影分类设置价格
                await setPrice(isp_code, 0, res, 'fastPrice');//按卡券分类设置价格
                await setPrice(isp_code, 0, res, 'originPrice');//按卡券分类设置价格
                await setPrice(isp_code, 0, res, 'sourcePrice');//按卡券分类设置价格
                break;
            case 'session':
                await setPrice(isp_code, 0, res, 'price');//按电影分类设置价格
                await setPrice(isp_code, 0, res, 'fastPrice');//按卡券分类设置价格
                await setPrice(isp_code, 0, res, 'originPrice');//按卡券分类设置价格
                await setPrice(isp_code, 0, res, 'sourcePrice');//按卡券分类设置价格
                break;
            case 'list':
                if (ctx.params.category == 'gift_card') {
                    await setPrice(isp_code, 1, res, 'sellPrice');//按卡券分类设置价格
                }
                break;
            case 'list_by_cat':
                if (ctx.params.category == 'gift_card') {
                    await setPrice(isp_code, 1, res, 'sellPrice');//按卡券分类设置价格
                    await setPrice(isp_code, 1, res, 'originalPrice');//按卡券分类设置价格
                }
                break;
            // case 'detail':
            //     await setPrice(isp_code, 2, res, 'sellPrice');//按卡券分类设置价格
            //     break;
            case 'menus':
                await setPrice(isp_code, 5, res, 'priceHead');//按餐饮分类设置价格
                await setPrice(isp_code, 5, res, 'price'); //按电影分类设置价格
                await setPrice(isp_code, 5, res, 'product_price');//按卡券分类设置价格
                await setPrice(isp_code, 5, res, 'sellPrice');//按卡券分类设置价格
                break;
            // case 'kfc':
            //     await setPrice(isp_code, 5, res, 'priceHead');//按餐饮分类设置价格
            //     await setPrice(isp_code, 5, res, 'price'); //按电影分类设置价格

            //     break;
            case 'goods_detail':
                if (ctx.params.category == 'kfc') {
                    await setPrice(isp_code, 5, res, 'product_price');//按卡券分类设置价格
                    console.log('kfc:', ctx.params.category + '/' + ctx.params.command);
                    await setPrice(isp_code, 5, res, 'salePrice');//按卡券分类设置价格
                }
            default:
                break;
        }
        ctx.body = res;
        return;
    }
    else {
        if (ctx.params.category == 'movie' && ctx.params.command == 'create_order2') {
            if (ctx.request.body.isp_code == undefined) {
                ctx.body = response.fail('isp_code不能为空');
                return;
            }
            // if (ctx.request.body.totalUserPrice == undefined) {
            //     ctx.body = response.fail('totalUserPrice不能为空');
            //     return;
            // }
            ctx.request.body.appId = APPID;
            ctx.request.body.pushUrl = `${BASEURL}/isp/notice/movie`
        } else if (ctx.params.category == 'gift_card' && ctx.params.command == 'create_order') {
            if (ctx.request.body.isp_code == undefined) {
                ctx.body = response.fail('isp_code不能为空');
                return;
            }
            ctx.request.body.appId = APPID;
            ctx.request.body.pushUrl = `${BASEURL}/isp/notice/giftcard`;

        } else if (ctx.params.category == 'kfc' && ctx.params.command == 'create_order') {
            if (ctx.request.body.isp_code == undefined) {
                ctx.body = response.fail('isp_code不能为空');
                return;
            }
            ctx.request.body.appId = APPID;
            ctx.request.body.pushUrl = `${BASEURL}/isp/notice/food`;
        }



        console.log('向奇票透传请求参数', JSON.stringify(ctx.request.body));
        res = await qipiao_post(path, ctx.request.body);
        if (res.err) {
            ctx.body = response.fail(res.err);
            return;
        }
        if (ctx.params.category == 'movie' && ctx.params.command == 'create_order2') {
            ctx.request.body.appId = APPID;
            ctx.request.body.pushUrl = `${config.baseUrl}/isp/notice/movie`
            if (res.code == 0) {
                console.log('创建电影订单成功', '入参', JSON.stringify(ctx.request.body), '出参', JSON.stringify(res));
                //创建电影订单成功 写入记录
                let movieRecord = {
                    id: res.data.orderId,
                    state: 0,
                    isp_code: ctx.request.body.isp_code,
                    create_time: moment().format('YYYY-MM-DD HH:mm:ss'),
                    seatNames: ctx.request.body.seatNames,
                    totalOrigin: ctx.request.body.originPrice,   //原始单价
                    postImageUrl: ctx.request.body.postImageUrl,
                    hallName: ctx.request.body.hallName,
                    movieStartTime: ctx.request.body.sessionBeginTime,
                    movieEndTime: ctx.request.body.sessionEndTime,
                    min_pay_amt: ctx.request.body.originPrice * 100 //有票票原价
                    // cost:res.data.totalUserPrice,
                    //profit:ctx.request.body.totalPrice-res.data.totalUserPrice,
                }
                //如果有多个座位 totalPrice要乘以对应的座位数   多个座位用逗号分隔
                if (ctx.request.body.seatNames.indexOf(',') > 0) {
                    movieRecord.totalOrigin *= ctx.request.body.seatNames.split(',').length;
                }
                if (ctx.request.body.movieName) movieRecord.movieName = ctx.request.body.movieName;
                if (ctx.request.body.cinemaName) movieRecord.cinemaName = ctx.request.body.cinemaName;
                let db_res = await db.create('movie_order', movieRecord);
                if (db_res instanceof Error) {
                    console.error('创建电影订单出错:' + db_res.message, '订单信息:', JSON.stringify(movieRecord));
                    ctx.body = response.fail('创建电影订单出错:' + db_res.message);
                    return;
                }
                ctx.body = res;
                return;
            }
            else {
                //奇票创建订单失败
                console.warn(`奇票创建订单失败 入参${JSON.stringify(ctx.request.body)},出参${JSON.stringify(res)}}`,);
            }
        } else if (ctx.params.category == 'gift_card' && ctx.params.command == 'create_order') {
            if (res.code == 0) {
                console.log('创建卡券订单成功', '入参', JSON.stringify(ctx.request.body), '出参', JSON.stringify(res));
                //获取商品详情
                let giftCardDetail = await qipiao_get('/api/gift_card/detail', { id: ctx.request.body.giftCardId });
                if (giftCardDetail.err) {
                    console.error('查询卡券商品信息失败:', giftCardDetail.err, '请求入参:', JSON.stringify(ctx.request.body));
                    ctx.body = response.fail(giftCardDetail.err);
                    return;
                }
                let imageUrl = giftCardDetail.data.logoUrl;

                //创建卡券订单成功 写入记录
                let giftRecord = {
                    id: res.data.orderId,
                    state: 0,
                    isp_code: ctx.request.body.isp_code,
                    orderName: res.data.title,
                    create_time: moment().format('YYYY-MM-DD HH:mm:ss'),
                    refundAmount: 0,
                    refundId: '',
                    imageUrl,
                    min_pay_amt: res.data.totalPrice * 100  //有票票件原价
                }
                let db_res = await db.create('gift_card_order', giftRecord);
                if (db_res instanceof Error) {
                    console.error('创建卡券订单出错:' + db_res.message, '订单信息:', JSON.stringify(giftRecord));
                    ctx.body = response.fail('创建卡券订单出错:' + db_res.message);
                    return;
                }
            } else {
                console.warn(`奇票创建卡券订单失败 入参${JSON.stringify(ctx.request.body)},出参${JSON.stringify(res)}}`,);
            }
        } else if (ctx.params.category == 'kfc' && ctx.params.command == 'create_order') {
            if (res.code == 0) {
                console.log('创建餐饮订单成功', '入参', JSON.stringify(ctx.request.body), '出参', JSON.stringify(res));
                //创建餐饮订单成功 写入记录
                let foodRecord = {
                    id: res.data.orderId,
                    state: 0,
                    isp_code: ctx.request.body.isp_code,
                    // orderName: res.data.title,
                    create_time: moment().format('YYYY-MM-DD HH:mm:ss'),
                    //   totalOrigin: ctx.request.body.originPrice,   //原始单价
                    products: ctx.request.body.products,
                    refundAmount: 0,
                    min_pay_amt: res.data.payAmount * 100,  //有票票支付金额
                    refundId: '',
                }
                if (ctx.request.body.products) {

                    foodRecord.products = JSON.stringify(ctx.request.body.products);

                }
                let db_res = await db.create('food_order', foodRecord);
                if (db_res instanceof Error) {
                    console.error('创建餐饮订单出错:' + db_res.message, '订单信息:', JSON.stringify(foodRecord));
                    ctx.body = response.fail('创建餐饮订单出错:' + db_res.message);
                    return;
                }
            } else {
                console.warn(`奇票创建餐饮订单失败 入参${JSON.stringify(ctx.request.body)},出参${JSON.stringify(res)}}`,);
            }
        }
    }


    ctx.body = res;
});

//回调通用接口
router.all('/isp/notice/:scene', async (ctx) => {
    //打印场景值
    console.log(ctx.params.scene);
    //打印请求方式是post还是get
    console.log(ctx.request.method);
    if (ctx.request.method == 'GET') {
        //打印get请求参数
        console.log(ctx.request.query);
        ctx.status = 200;
        ctx.body = 'success'
    }
    else {
        //获取请求头中的时间戳 和签名
        console.log(JSON.stringify(ctx.request.body));
        const timestamp = ctx.request.header.timestamp;
        const signature = ctx.request.header.signature;
        const pushJson = JSON.stringify(ctx.request.body);
        console.log('时间戳:', timestamp, '签名:', signature, '请求参数:', pushJson);
        //验证签名
        if (ctx.request.body == undefined) {
            ctx.status = 400;
            ctx.body = '请求参数为空';
            return;
        }
        // const isValid = verifySignature(pushJson, timestamp, signature);
        // if (!isValid) {
        //     console.log('签名验证失败');
        //     ctx.body = '签名验证失败';
        //     return;
        // }
        let data = ctx.request.body.data;
        //打印post请求参数
        switch (ctx.params.scene) {
            case 'movie':
                //查询电影订单
                let db_movie_order = await db.findOne('movie_order', { id: data.id });
                if (db_movie_order instanceof Error) {
                    ctx.body = '数据库查询错误:' + db_movie_order.message;
                    return;
                }
                if (db_movie_order == null) {
                    console.warn('电影订单不存在', '订单信息:', JSON.stringify(data));
                    ctx.body = '电影订单不存在';
                    return;
                }
                if (data.state == '4') //出票成功
                {
                    let cost = parseFloat(data.totalEpPrice);//本单成本
                    let profit = db_movie_order.pay_amount - cost;//本单利润
                    let totalCommission = parseFloat(data.totalCommission);//佣金
                    let updateTime = moment().format('YYYY-MM-DD HH:mm:ss');
                    let ticketCodeText = data.ticketCodeText;
                    let ticketCode = data.ticketCode;
                    // let movieStartTime = data.movieStartTime;
                    // let movieEndTime = data.movieEndTime;
                    //更新电影订单状态
                    let res = await db.update('movie_order',
                        { state: data.state, cost: cost, profit, totalCommission, updateTime, ticketCodeText, ticketCode }
                        , { id: data.id }
                    );
                    if (res instanceof Error) {
                        ctx.body = '数据库更新错误:' + res.message;
                        console.error('更新电影订单状态出错:' + res.message, '订单信息:', JSON.stringify(data));
                        return;
                    }

                }
                else if (data.state == '7') //退票成功
                {
                    let refundAmount = parseFloat(data.refundAmount);//退款金额
                    let totalCommission = parseFloat(data.totalCommission);//佣金
                    let updateTime = moment().format('YYYY-MM-DD HH:mm:ss');
                    let remark = data.ticketCodeText;
                    //更新电影订单状态
                    let res = await db.update('movie_order',
                        { state: data.state, refundAmount, totalCommission, updateTime, remark }
                        , { id: data.id }
                    );
                    if (res instanceof Error) {
                        ctx.body = '数据库更新错误:' + res.message;
                        console.error('更新电影订单状态出错:' + res.message, '订单信息:', JSON.stringify(data));
                        return;
                    }
                }
                break;
            case 'gift_card':

                //查询卡券订单的状态
                let detail = await qipiao_get('/api/gift_card/order_detail', { id: data.id });
                if (detail.err) {
                    console.error('查询卡券订单状态失败:', detail.err, '回调信息:', JSON.stringify(data));
                    ctx.body = response.fail(detail.err);
                    return;
                }


                //查询卡券订单
                let db_gift_card_order = await db.findOne('gift_card_order', { id: data.id });
                if (db_gift_card_order instanceof Error) {
                    console.error('卡券回调数据库查询错误:', db_gift_card_order.message, '回调信息:', JSON.stringify(data));
                    ctx.body = '数据库查询错误:' + db_gift_card_order.message;
                    return;
                }
                db_gift_card_order.state = detail.data.orderState;
                db_gift_card_order.updateTime = detail.data.updateTime;
                db_gift_card_order.commission = detail.data.epCommission;
                db_gift_card_order.cost = detail.data.totalEpPrice;
                res = await db.update('gift_card_order', db_gift_card_order, { id: data.id });
                if (res instanceof Error) {
                    console.error('卡券回调数据库更新错误:', res.message, '回调信息:', JSON.stringify(db_gift_card_order))
                    ctx.body = '数据库写入错误:' + res.message;
                    return;
                }
                break;
            case 'food':
                let db_food_order = await db.findOne('food_order', { id: data.id });
                if (db_food_order instanceof Error) {
                    console.error('点餐回调数据库查询错误:', db_food_order.message, '回调信息:', JSON.stringify(data));
                    ctx.body = '数据库查询错误:' + db_food_order.message;
                    return;
                }
                db_food_order.state = data.orderState;
                db_food_order.updateTime = data.updateTime;
                if (data.orderState == 3) {
                    db_food_order.commission = data.commission;
                    db_food_order.cost = data.totalUserPrice - data.commission;
                    //db_food_order.profit = db_food_order.pay_amount - db_food_order.cost;//本单利润
                }
                else {
                    db_food_order.commission = 0;
                    db_food_order.cost = 0;
                    //     db_food_order.profit = 0;
                }
                delete db_food_order.products;
                let res = await db.update('food_order', db_food_order, { id: data.id });
                if (res instanceof Error) {
                    console.error('点餐回调数据库更新错误:', res.message, '回调信息:', JSON.stringify(db_food_order))
                    ctx.body = '数据库写入错误:' + res.message;
                    return;
                }
                break;
            case 'groupbuy':
                //查询团购订单
                let db_groupbuy_order = await db.findOne('group_buy_order', { id: data.id });
                if (db_groupbuy_order instanceof Error) {
                    ctx.body = '数据库查询错误:' + db_groupbuy_order.message;
                    return;
                }
                let groupbuyRecord = {
                    state: data.orderState,
                    id: data.id,
                    totalUserPrice: data.totalUserPrice,
                    commission: data.commission,
                    orderName: data.orderName,
                    codes: data.codes,
                    refundAmount: data.refundAmount,
                    refundId: data.refundId,
                    totalOriginPrice: data.totalOriginPrice,
                    create_time: data.createTime,
                    updateTime: data.updateTime,

                }
                if (db_groupbuy_order == null) {
                    let res = await db.create('group_buy_order', groupbuyRecord);
                    if (res instanceof Error) {
                        ctx.body = '数据库写入错误:' + res.message;
                        return;
                    }
                } else {
                    let res = await db.update('group_buy_order', { id: data.id }, groupbuyRecord);
                    if (res instanceof Error) {
                        ctx.body = '数据库更新错误:' + res.message;
                        return;
                    }
                }
                break;
            default:
                break;
        }
        ctx.status = 200;
        ctx.body = 'success'
    }
});


router.post('/isp/api/appidGetToken', async (ctx) => {
    let body = ctx.request.body;
    if (body.appid == null || body.appid == undefined) {
        ctx.body = response.fail('appid不能为空');
        return;
    }
    let isp = await db.findOne('isp_info', { appid: body.appid });
    if (isp instanceof Error) {
        console.log(isp);
        ctx.body = response.fail('数据库查询错误:' + isp.message);
        return;
    }

    if (isp == null) {
        ctx.body = response.fail('appid错误');
        return;
    }
    if (isp.enable != 1) {
        ctx.body = response.fail('该账号已被禁用');
        return;
    }
    const { token, expireTime } = await createToken({
        name: isp.name,
        code: isp.isp_code,
        type: 'isp'
    });
    ctx.body = response.success({ token: token, expireTime: expireTime });
});

router.post('/isp/api/getToken', async (ctx) => {
    let body = ctx.request.body;
    if (body.appid == null || body.appid == undefined) {
        ctx.body = response.fail('appid不能为空');
        return;
    }
    if (body.secret == null || body.secret == undefined) {
        ctx.body = response.fail('secret不能为空');
        return;
    }
    let isp = await db.findOne('isp_info', { appid: body.appid, secret: body.secret });
    if (isp instanceof Error) {
        console.log(isp);
        ctx.body = response.fail('数据库查询错误:' + isp.message);
        return;
    }

    if (isp == null) {
        ctx.body = response.fail('appid或secret错误');
        return;
    }
    if (isp.enable != 1) {
        ctx.body = response.fail('该账号已被禁用');
        return;
    }
    const { token, expireTime } = await createToken({
        name: isp.name,
        code: isp.isp_code,
        type: 'isp'
    });
    ctx.body = response.success({ token: token, expireTime: expireTime });
});


//服务商查询可用的券包列表
router.post('/isp/api/ticket/packet/list', async (ctx) => {
    let body = ctx.request.body;
    let isp_code = ctx.state.isp.code;
    if (isp_code == undefined) {
        ctx.body = response.fail('请先获取token');
        return;
    }
    const sql = `SELECT ticket_packet.name AS ticket_packet_name,ticket_packet_id, DATE_FORMAT(CONVERT_TZ(expire_time, '+00:00', '+08:00'), '%Y-%m-%d %H:%i:%s') AS expire_time , inventory
        FROM isp_ticket_stock
        LEFT JOIN ticket_packet
        ON isp_ticket_stock.ticket_packet_id = ticket_packet.id
        WHERE isp_ticket_stock.isp_code = '${isp_code}' AND inventory > 0 AND expire_time > '${moment().format('YYYY-MM-DD HH:mm:ss')}';
    `;
    let packets = await db.query(sql, []);
    if (packets instanceof Error) {
        ctx.body = response.fail('查询券包出错:' + packets.message);
        return;
    }
    ctx.body = response.success(packets);
});

//服务商查询可用的代金券库存列表
router.post('/isp/api/voucher/voucher/list', async (ctx) => {
    let isp_code = ctx.state.isp.code;
    if (isp_code == undefined) {
        ctx.body = response.fail('请先获取token');
        return;
    }
    const sql = `SELECT id, DATE_FORMAT(CONVERT_TZ(expire_time, '+00:00', '+08:00'), '%Y-%m-%d %H:%i:%s') AS expire_time , inventory
        FROM isp_voucher_stock  WHERE isp_code = '${isp_code}' AND inventory > 0 AND expire_time > '${moment().format('YYYY-MM-DD HH:mm:ss')} order by creat_time asc';
    `;
    let packets = await db.query(sql, []);
    if (packets instanceof Error) {
        console.error('查询可用的代金券库存列表出错:', packets.message, 'sql:', sql, 'isp_code:', isp_code);
        ctx.body = response.fail('查询可用的代金券库存列表出错:' + packets.message);
        return;
    }
    ctx.body = response.success(packets);
});

//服务商给用户发放优惠券
router.post('/isp/api/user/coupon/grant', async (ctx) => {
    let body = ctx.request.body;
    
    let isp_code = ctx.state.isp.code;
    if (isp_code == undefined) {
        ctx.body = response.fail('请先获取token');
        return;
    }
    if (body.ticket_packet_id == null || body.ticket_packet_id == undefined) {
        ctx.body = response.fail('ticket_packet_id不能为空');
        return;
    }
    if (body.phone == undefined && body.openid == undefined) {
        ctx.body = response.fail('phone或openid不能为空');
        return;
    }
    //给第三方发放券包时防止重复发放
    if (body.release_sn) {
        //检查是否有重复发放的记录
        let Exist = await db.checkExist('user_ticket', { release_sn: body.release_sn, isp_code: isp_code });
        if (Exist instanceof Error) {
            ctx.body = response.fail('查询重复发放记录出错:' + Exist.message);
            return;
        }
        if (Exist) {
            ctx.body = response.custom(2,'已存在相同发行流水号的发放记录');
            return;
        }
    }
    let conn = await db.startTransaction();
    if (conn instanceof Error) {
        ctx.body = response.fail('开始事务出错:' + conn.message);
        return;
    }
    let sqls = [];
    try {
        //查询是否有对应的券包的库存
        let sql = `select * from isp_ticket_stock where isp_code=${isp_code} and ticket_packet_id=${body.ticket_packet_id} and 
    inventory>0 and expire_time>'${moment().format('YYYY-MM-DD HH:mm:ss')}'`;
        console.log(sql);
        let stocks = await db.query(sql);
        if (stocks instanceof Error) {
            ctx.body = response.fail('查询服务商库存出错:' + stocks.message);
            return;
        }
        if (stocks.length == 0) {
            ctx.body = response.fail('库存不足');
            return;
        }
        //如果有多条券包库存，优先使用最早过期的
        stocks.sort((a, b) => {
            return new Date(a.expire_time) - new Date(b.expire_time);
        });
        let stock = stocks[0]
    
        let packet = await db.findOne('ticket_packet', { id: body.ticket_packet_id });
        if (packet instanceof Error) {
            ctx.body = response.fail('查询券包出错:' + packet.message);
            return;
        }
        sql = `select ticket_packet_details.ticket_type_id,ticket_num,ticket_type.* from ticket_packet_details  
    LEFT JOIN  ticket_type on ticket_packet_details.ticket_type_id =ticket_type.id  where packet_id=${body.ticket_packet_id}`;
        let details = await db.query(sql);
        if (details instanceof Error) {
            ctx.body = response.fail('查询券包明细出错:' + details.message);
            return;
        }
        if (details.length == 0) {
            ctx.body = response.fail('券包明细为空');
            return;
        }

        for (let i = 0; i < details.length; i++) {
            let grant_record = {
                isp_code: isp_code,
                packet_id: body.ticket_packet_id,
                type_id: details[i].ticket_type_id,
                num: details[i].ticket_num,
                use_num: 0,
                brand_id: details[i].brand_id,
                category_id: details[i].category_id,
                release_time: moment().format('YYYY-MM-DD HH:mm:ss'),
            }
            if(body.release_sn) grant_record.release_sn = body.release_sn;
            if (body.phone) grant_record.phone = body.phone;
            if (body.openid) grant_record.openid = body.openid;
            switch (details[i].expirse_days) {
                case -1:   //月底过期
                    grant_record.expire_time = moment().endOf('month').format('YYYY-MM-DD 23:59:59');
                    break;
                case -2: //周末过期
                    grant_record.expire_time = moment().endOf('week').format('YYYY-MM-DD 23:59:59');
                    break;
                case -3: //年底过期
                    grant_record.expire_time = moment().endOf('year').format('YYYY-MM-DD 23:59:59');
                    break;
                case -4:  //指定日期过期
                    grant_record.expire_time = details[i].expirse_date;
                    break;
                case 0:  //永久有效
                    grant_record.expire_time = '2099-12-31 23:59:59';
                    break;
                default:  //指定天数过期
                    grant_record.expire_time = moment().add(details[i].expirse_days, 'days').format('YYYY-MM-DD 23:59:59');
            }
            sqls.push({ sql: 'insert into user_ticket set ?', param: grant_record });
        }
        //写入服务商券包变更记录
        let change_record = {
            isp_code: isp_code,
            stock_id: stock.id,
            ticket_packet_id: body.ticket_packet_id,
            change_time: moment().format('YYYY-MM-DD HH:mm:ss'),
            balance: stock.inventory,
            change_value: -1,
            inventory: stock.inventory - 1,
            oper_type: 1, //用户领券
            // change_value: -details.length,
            // inventory: stock.inventory - details.length,
            // oper_type: 3,//发放
        }
        if (body.phone) change_record.user_phone = body.phone;
        if (body.openid) change_record.user_openid = body.openid;
        sqls.push({ sql: 'insert into isp_ticket_change_record set ?', param: change_record });
        //更新服务商券包库存
        if (stock.inventory - 1 == 0) {
            console.log('服务包券包库存为0，自动删除');
            sqls.push({ sql: `delete from isp_ticket_stock where id=${stock.id}` });
        } else {
            sqls.push({ sql: `update isp_ticket_stock set inventory=inventory-1 where id=${stock.id}` });
        }
    }
    catch (e) {
        console.log('执行行级锁查询出错', e);
        await db.rollback(conn);
        ctx.body = response.fail('发放失败:' + e.message);
        return;
    }
    let res = await db.doTransaction(conn, sqls);
    if (res instanceof Error) {
        ctx.body = response.fail('给用户发券事务写入错误:' + res.message);
        return;
    }
    ctx.body = response.success('发放成功');

});
router.post('/isp/api/user/voucher/grant', async (ctx) => {
    let body = ctx.request.body;
    let isp_code = ctx.state.isp.code;
    if (isp_code == undefined) {
        ctx.body = response.fail('请先获取token');
        return;
    }
    if(body.num == undefined || body.num == 0){
        ctx.body = response.fail('num不能为空且不能为0');
        return;
    }
    if (body.phone == undefined && body.openid == undefined) {
        ctx.body = response.fail('phone或openid不能为空');
        return;
    }
    //给第三方发放券包时防止重复发放
    if (body.release_sn) {
        //检查是否有重复发放的记录
        let Exist = await db.checkExist('user_voucher', { release_sn: body.release_sn, isp_code: isp_code });
        if (Exist instanceof Error) {
            console.error('查询重复发放代金券记录出错:', Exist.message, '请求参数:', JSON.stringify(body), 'isp_code:', isp_code);
            ctx.body = response.fail('查询重复发放代金券记录出错:' + Exist.message);
            return;
        }
        if (Exist) {
            ctx.body = response.custom(2,'已存在相同发行流水号的发放记录');
            return;
        }
    }
    let conn = await db.startTransaction();
    if (conn instanceof Error) {
        ctx.body = response.fail('开始事务出错:' + conn.message);
        return;
    }
    let sqls = [];
    try {
        //查询是否有对应的券包的库存
        let sql = `select * from isp_voucher_stock where isp_code=${isp_code}  and  inventory>0 and expire_time>'${moment().format('YYYY-MM-DD HH:mm:ss')}'`;
        console.log(sql);
        let stocks = await db.query(sql);
        if (stocks instanceof Error) {
            console.error('查询服务商代金券库存出错:', stocks.message, 'sql:', sql, 'isp_code:', isp_code);
            ctx.body = response.fail('查询服务商库存出错:' + stocks.message);
            return;
        }
        if (stocks.length == 0) {
            console.warn('库存不足',  '请求sql:', sql, 'isp_code:', isp_code);
            ctx.body = response.fail('库存不足');
            return;
        }
        //如果有多条券包库存，优先使用最早过期的
        stocks.sort((a, b) => {
            return new Date(a.expire_time) - new Date(b.expire_time);
        });
        let stock = null;
        for(let i=0;i< stocks.length;i++){
            if(stocks[i].inventory >= body.num){
                stock = stocks[i];
                break;
            }
        }
        if(stock == null){
            console.warn('库存不足',  '请求sql:', sql, 'isp_code:', isp_code);
            ctx.body = response.fail('库存不足');
            throw new Error('未找到与发放数量匹配的库存');
        }
        let grant_record = {
            isp_code: isp_code,
            num: body.num,
            use_num: 0,
            expire_time: stock.expire_time,
            release_time: moment().format('YYYY-MM-DD HH:mm:ss'),
        }
        if(body.release_sn) grant_record.release_sn = body.release_sn;
        if (body.phone) grant_record.phone = body.phone;
        if (body.openid) grant_record.openid = body.openid;
        sqls.push({ sql: 'insert into user_voucher set ?', param: grant_record });


   
        //写入服务商券包变更记录
        let change_record = {
            isp_code: isp_code,
            stock_id: stock.id,
            change_time: moment().format('YYYY-MM-DD HH:mm:ss'),
            balance: stock.inventory,
            change_value: -body.num,
            inventory: stock.inventory - body.num,
            oper_type: 1, //用户领券
        }
        if (body.phone) change_record.user_phone = body.phone;
        if (body.openid) change_record.user_openid = body.openid;
        sqls.push({ sql: 'insert into isp_voucher_change_record set ?', param: change_record });
        //更新服务商券包库存
        if (stock.inventory - 1 == 0) {
            console.log('服务包券包库存为0，自动删除');
            sqls.push({ sql: `delete from isp_voucher_stock where id=${stock.id}` });
        } else {
            sqls.push({ sql: `update isp_voucher_stock set inventory=inventory-${body.num} where id=${stock.id}` });
        }
    }
    catch (e) {
        console.log('执行行级锁查询出错', e);
        await db.rollback(conn);
        ctx.body = response.fail('发放失败:' + e.message);
        return;
    }
    let res = await db.doTransaction(conn, sqls);
    if (res instanceof Error) {
        ctx.body = response.fail('给用户发代金券事务写入错误:' + res.message);
        return;
    }
    ctx.body = response.success('发放成功');

});

//服务商删除用户券包
router.post('/isp/api/user/coupon/removeUserTicket', async (ctx) => {
    let body = ctx.request.body;
    let isp_code = ctx.state.isp.code;
    if (body == undefined) {
        ctx.body = response.fail('参数不允许为空');
        return
    }
    if (body?.phone === undefined && body?.phone == '') {
        ctx.body = response.fail('phone不能为空');
        return;
    }
    if (body?.packet_id == undefined && body?.packet_id == '') {
        ctx.body = response.fail('packet_id不能为空');
        return;
    }
    //删除指定用户的下所有packet_id与入参一致的券包
    let deletesql = `delete from user_ticket where isp_code=${isp_code} and phone='${body.phone}' and packet_id=${body.packet_id}`;
    let res = await db.query(deletesql);
    if (res instanceof Error) {
        ctx.body = response.fail('删除用户券包出错:' + res.message);
        return;
    }
    ctx.body = response.success('删除成功');
});



//服务商查询用户券包
router.post('/isp/api/user/coupon/query', async (ctx) => {
    let body = ctx.request.body;
    let isp_code = ctx.state.isp.code;
    if (body == undefined) {
        ctx.body = response.fail('参数不允许为空');
        return
    }
    if (body?.phone === undefined && body?.openid === undefined) {
        ctx.body = response.fail('phone或openid不能为空');
        return;
    }
    if (body.phone) {
        let sql = `select user_ticket.*,ticket_type.name,ticket_type.discount,ticket_type.images, ticket_type.expirse_days,
        ticket_type.describe,ticket_type.use_interval,ticket_type.product_list,ticket_type.expirse_date,ticket_type.brand_id,ticket_type.category_id,
        ticket_type.brand_url,ticket_type.min_limit         from user_ticket   left join ticket_type  on  user_ticket.type_id = ticket_type.id  where isp_code=${isp_code} and phone='${body.phone}'
         and num>0   and  expire_time > '${moment().format('YYYY-MM-DD HH:mm:ss')}'`;
        let tickets = await db.query(sql);
        if (tickets instanceof Error) {
            ctx.body = response.fail('查询用户券包出错:' + tickets.message);
            return;
        }
        ctx.body = response.success(tickets);
        return;
    } else if (body?.openid) {
        let sql = `select user_ticket.*,ticket_type.name,ticket_type.discount,ticket_type.images, ticket_type.expirse_days,
        ticket_type.describe,ticket_type.use_interval,ticket_type.product_list,ticket_type.expirse_date,ticket_type.brand_id,ticket_type.category_id,
        ticket_type.brand_url ,ticket_type.min_limit    from user_ticket   left join ticket_type  on  user_ticket.type_id = ticket_type.id  where isp_code=${isp_code} and openid='${body.openid}' 
        and num>0 and  expire_time > '${moment().format('YYYY-MM-DD HH:mm:ss')}'`;
        let tickets = await db.query(sql);
        if (tickets instanceof Error) {
            ctx.body = response.fail('查询用户券包出错:' + tickets.message);
            return;
        }
        ctx.body = response.success(tickets);
        return;
    }
});


router.post('/isp/api/user/coupon/query_ex', async (ctx) => {
    let body = ctx.request.body;
    let isp_code = ctx.state.isp.code;
    if (body == undefined) {
        ctx.body = response.fail('参数不允许为空');
        return
    }
    if (body?.phone === undefined && body?.openid === undefined) {
        ctx.body = response.fail('phone或openid不能为空');
        return;
    }
    if (body.phone) {
        let sql = `select user_ticket.id,user_ticket.type_id,user_ticket.num,user_ticket.use_num ,user_ticket.phone,user_ticket.openid,
        DATE_FORMAT(user_ticket.expire_time, '%Y-%m-%d %H:%i:%s') AS expire_time,
        ticket_type.name,ticket_type.discount,ticket_type.brand_url, ticket_type.brand_id as brand  ,ticket_type.category_id as category,
    ticket_type.min_limit         from user_ticket   left join ticket_type  on  user_ticket.type_id = ticket_type.id  where isp_code=${isp_code} and phone='${body.phone}'
         and num>0   and  expire_time > '${moment().format('YYYY-MM-DD HH:mm:ss')}'`;
        let tickets = await db.query(sql);
        if (tickets instanceof Error) {
            ctx.body = response.fail('查询用户券包出错:' + tickets.message);
            return;
        }
        ctx.body = response.success(tickets);
        return;
    } else if (body?.openid) {
        let sql = `select user_ticket.id,user_ticket.type_id,user_ticket.num,user_ticket.use_num ,user_ticket.phone,user_ticket.openid,
        DATE_FORMAT(user_ticket.expire_time, '%Y-%m-%d %H:%i:%s') AS expire_time,
        ticket_type.name,ticket_type.discount,ticket_type.brand_url, ticket_type.brand_id as brand  ,ticket_type.category_id as category,
    ticket_type.min_limit         from user_ticket   left join ticket_type  on  user_ticket.type_id = ticket_type.id  where isp_code=${isp_code} and openid='${body.openid}' 
        and num>0 and  expire_time > '${moment().format('YYYY-MM-DD HH:mm:ss')}'`;
        let tickets = await db.query(sql);
        if (tickets instanceof Error) {
            ctx.body = response.fail('查询用户券包出错:' + tickets.message);
            return;
        }
        ctx.body = response.success(tickets);
        return;
    }
});

//服务商查询用户的可用代金券
router.post('/isp/api/user/voucher/query', async (ctx) => {
    let body = ctx.request.body;
    let isp_code = ctx.state.isp.code;
    if (body == undefined) {
        ctx.body = response.fail('参数不允许为空');
        return
    }
    if (body?.phone === undefined && body?.openid === undefined) {
        ctx.body = response.fail('phone或openid不能为空');
        return;
    }
    if (body.phone) {
        let sql = `select id,isp_code,phone,openid ,num,use_num,release_sn, DATE_FORMAT(CONVERT_TZ(expire_time, '+00:00', '+08:00'), '%Y-%m-%d %H:%i:%s') AS expire_time 
            FROM user_voucher where isp_code=${isp_code} and phone='${body.phone}'  and num>0   and  expire_time > '${moment().format('YYYY-MM-DD HH:mm:ss')}'`;
        let tickets = await db.query(sql);
        if (tickets instanceof Error) {
            console.error('查询用户代金券包出错:', tickets.message, 'sql:', sql, 'isp_code:', isp_code);
            ctx.body = response.fail('查询用户代金券包出错:' + tickets.message);
            return;
        }
        ctx.body = response.success(tickets);
        return;
    } else if (body?.openid) {
        let sql = `select id,isp_code,phone,openid ,num,use_num,release_sn, DATE_FORMAT(CONVERT_TZ(expire_time, '+00:00', '+08:00'), '%Y-%m-%d %H:%i:%s') AS expire_time
             FROM user_voucher where isp_code=${isp_code} and openid='${body.openid}'  and num>0   and  expire_time > '${moment().format('YYYY-MM-DD HH:mm:ss')}'`;
        let tickets = await db.query(sql);
        if (tickets instanceof Error) {
            console.error('查询用户代金券出错:', tickets.message, 'sql:', sql, 'isp_code:', isp_code);
            ctx.body = response.fail('查询用户代金券出错:' + tickets.message);
            return;
        }
        ctx.body = response.success(tickets);
        return;
    }
});



export { router };
